package com.coolwen.controller;

import com.coolwen.entity.*;
import com.coolwen.service.councilor.CouncilorService;
import com.coolwen.service.expert.ExpertService;
import com.coolwen.service.student.StudentService;
import com.coolwen.service.total.TotalService;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by nieyu on 2016/8/16.
 */
@Controller
@RequestMapping("/total")
public class QueryTotalMessageController {

    @Autowired
    StudentService studentService;

    @Autowired
    ExpertService expertService;

    @Autowired
    CouncilorService councilorService;

    @Autowired
    TotalService totalService;

    @RequestMapping(value = "/total_info", method = RequestMethod.GET)
    public String queryAllInfo(Model model, String department, String major, String grade, HttpServletRequest request) {

        model.addAttribute("g", grade);
        model.addAttribute("d", department);
        model.addAttribute("m", major);
        model.addAttribute("pagers", totalService.find(grade, department, major));

        return "page/total";
    }

    /*
    *评教汇总信息导出
     */
    @RequestMapping(value = "/out_info", method = RequestMethod.GET)
    public String outTotalInfo(String grade, String department, String major, HttpServletResponse response, HttpServletRequest request) throws IOException, WriteException {

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流

        SimpleDateFormat dt = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = dt.format(new Date());//获取当前时间

        response.setHeader("Content-disposition", "attachment; filename=" + new String(date.getBytes("GB2312"), "8859_1") + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wwb;
        List<TotalInfo> totals = totalService.findAll(grade, department, major);

        try {
            //设置单元格文本自动换行
            WritableFont fontTitle = new WritableFont(WritableFont.TIMES, 9, WritableFont.NO_BOLD);
            WritableCellFormat formatTitle = new WritableCellFormat(fontTitle);
            formatTitle.setWrap(true);

            WritableCellFormat headerFormat = new WritableCellFormat(
                    fontTitle);

            //设置单元格文本居中显示

            //水平居中
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                    10,
                    WritableFont.BOLD,
                    false,
                    UnderlineStyle.NO_UNDERLINE);

            WritableCellFormat formatTitles = new WritableCellFormat(font);
            formatTitles.setWrap(true);

            WritableCellFormat headerFormats = new WritableCellFormat(
                    font);

            //设置单元格文本居中显示

            //水平居中
            headerFormats.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormats.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

            wwb = Workbook.createWorkbook(os);
            WritableSheet ws = wwb.createSheet("汇总", 0);

            ws.mergeCells(1, 0, 18, 1);//合并单元格，第一个参数：要合并的单元格最左上角的列号，
            // 第二个参数：要合并的单元格最左上角的行号，
            // 第三个参数：要合并的单元格最右角的列号，
            // 第四个参数：要合并的单元格最右下角的行号，
            ws.mergeCells(0, 0, 0, 1);

            ws.setColumnView(1, 15);

            for (int i = 0; i < 1; i++) {
                TotalInfo total = totals.get(i);
                ws.addCell(new Label(1, 0, "四川旅游学院本科(" + total.getMajor() + "专业）专业课程质量（学生/二级督导/专家组）评价统计表", headerFormats));
            }

            int g1 = Integer.parseInt(request.getParameter("grade"));
            int g2 = g1 + 1;
            int g3 = g1 + 2;
            int g4 = g1 + 3;

            ws.mergeCells(0, 2, 0, 3);
            ws.addCell(new Label(0, 2, "序号", headerFormat));
            ws.mergeCells(1, 2, 1, 3);
            ws.addCell(new Label(1, 2, "课程名称", headerFormat));
            ws.mergeCells(2, 2, 5, 2);
            ws.addCell(new Label(2, 2, grade + "级本科班", headerFormat));
            ws.addCell(new Label(2, 3, "学生评价"));
            ws.addCell(new Label(3, 3, "二级督导"));
            ws.addCell(new Label(4, 3, "校专家组"));
            ws.addCell(new Label(5, 3, "平均分数"));
            ws.mergeCells(6, 2, 9, 2);
            ws.addCell(new Label(6, 2, g2 + "级本科班", headerFormat));
            ws.addCell(new Label(6, 3, "学生评价"));
            ws.addCell(new Label(7, 3, "二级督导"));
            ws.addCell(new Label(8, 3, "校专家组"));
            ws.addCell(new Label(9, 3, "平均分数"));
            ws.mergeCells(10, 2, 13, 2);
            ws.addCell(new Label(10, 2, g3 + "级本科班", headerFormat));
            ws.addCell(new Label(10, 3, "学生评价"));
            ws.addCell(new Label(11, 3, "二级督导"));
            ws.addCell(new Label(12, 3, "校专家组"));
            ws.addCell(new Label(13, 3, "平均分数"));
            ws.mergeCells(14, 2, 17, 2);
            ws.addCell(new Label(14, 2, g4 + "级本科班", headerFormat));
            ws.addCell(new Label(14, 3, "学生评价"));
            ws.addCell(new Label(15, 3, "二级督导"));
            ws.addCell(new Label(16, 3, "校专家组"));
            ws.addCell(new Label(17, 3, "平均分数"));
            ws.mergeCells(18, 2, 18, 3);
            ws.addCell(new Label(18, 2, "总评得分", headerFormat));


            for (int i = 0; i < totals.size(); i++) {

                int b = i + 1;
                double tScore1 = 0;
                double tScore2 = 0;
                double tScore3 = 0;
                double tScore4 = 0;
                TotalInfo totalInfo = totals.get(i);
                ws.addCell(new Label(0, i + 4, "" + b, headerFormat));
                ws.addCell(new Label(1, i + 4, "" + totalInfo.getCourse()));

                for (int j = 0; j < totalInfo.getGrades().size(); j++) {
                    Grade grade1 = totalInfo.getGrades().get(j);
                    if (Integer.valueOf(grade1.getGrade()) == g1) {

                        for (int k = 0; k < grade1.getScores().size(); k++) {
                            Score score = grade1.getScores().get(k);
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(2, i + 4, "" + score.getsScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(2, i + 4, ""));
                            }
                            if (score.getcScore() != null) {
                                ws.addCell(new Label(3, i + 4, "" + score.getcScore(), headerFormat));
                            }
                            if (score.getcScore() == null) {
                                ws.addCell(new Label(3, i + 4, ""));
                            }
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(4, i + 4, "" + score.geteScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(4, i + 4, ""));
                            }

                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dscore = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3);
                                ws.addCell(new Label(5, i + 4, dscore, headerFormat));
                                tScore1 = (score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3;
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() == null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2);
                                ws.addCell(new Label(5, i + 4, dd, headerFormat));
                                tScore1 = (score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.getcScore()) / 1.7);
                                ws.addCell(new Label(5, i + 4, dd, headerFormat));
                                tScore1 = (score.getsScore() * 0.7 + score.getcScore()) / 1.7;
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.geteScore() * 1.3 + score.getcScore()) / 2.3);
                                ws.addCell(new Label(5, i + 4, dd, headerFormat));
                                tScore1 = (score.geteScore() * 1.3 + score.getcScore()) / 2.3;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() == null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format(score.getsScore());
                                ws.addCell(new Label(5, i + 4, dd, headerFormat));
                                tScore1 = score.getsScore();
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() == null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format(score.geteScore());
                                ws.addCell(new Label(5, i + 4, dd, headerFormat));
                                tScore1 = score.geteScore();
                            }
                            if (score.getsScore() == null && score.geteScore() == null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format(score.getcScore());
                                ws.addCell(new Label(5, i + 4, dd, headerFormat));
                                tScore1 = score.getcScore();
                            }

                        }
                        if (grade1.getScores().isEmpty()) {
                            ws.addCell(new Label(2, i + 4, ""));
                            ws.addCell(new Label(3, i + 4, ""));
                            ws.addCell(new Label(4, i + 4, ""));
                            ws.addCell(new Label(5, i + 4, ""));
                            tScore1 = 0;
                        }

                    }
                    if (Integer.valueOf(grade1.getGrade()) == g2) {

                        for (int k = 0; k < grade1.getScores().size(); k++) {
                            Score score = grade1.getScores().get(k);
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(6, i + 4, "" + score.getsScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(6, i + 4, ""));
                            }
                            if (score.getcScore() != null) {
                                ws.addCell(new Label(7, i + 4, "" + score.getcScore(), headerFormat));
                            }
                            if (score.getcScore() == null) {
                                ws.addCell(new Label(7, i + 4, ""));
                            }
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(8, i + 4, "" + score.geteScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(8, i + 4, ""));
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3);
                                ws.addCell(new Label(9, i + 4, dd, headerFormat));
                                tScore2 = (score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3;
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() == null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2);
                                ws.addCell(new Label(9, i + 4, dd, headerFormat));
                                tScore2 = (score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.getcScore()) / 1.7);
                                ws.addCell(new Label(9, i + 4, dd, headerFormat));
                                tScore2 = (score.getsScore() * 0.7 + score.getcScore()) / 1.7;
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.geteScore() * 1.3 + score.getcScore()) / 2.3);
                                ws.addCell(new Label(9, i + 4, dd, headerFormat));
                                tScore2 = (score.geteScore() * 1.3 + score.getcScore()) / 2.3;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() == null) {
                                ws.addCell(new Label(9, i + 4, "" + score.getsScore(), headerFormat));
                                tScore2 = score.getsScore();
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() == null) {
                                ws.addCell(new Label(9, i + 4, "" + score.geteScore(), headerFormat));
                                tScore2 = score.geteScore();
                            }
                            if (score.getsScore() == null && score.geteScore() == null && score.getcScore() != null) {
                                ws.addCell(new Label(9, i + 4, "" + score.getcScore(), headerFormat));
                                tScore2 = score.getcScore();
                            }

                        }
                        if (grade1.getScores().isEmpty()) {
                            ws.addCell(new Label(6, i + 4, ""));
                            ws.addCell(new Label(7, i + 4, ""));
                            ws.addCell(new Label(8, i + 4, ""));
                            ws.addCell(new Label(9, i + 4, ""));
                            tScore2 = 0;
                        }

                    }
                    if (Integer.valueOf(grade1.getGrade()) == g3) {

                        for (int k = 0; k < grade1.getScores().size(); k++) {
                            Score score = grade1.getScores().get(k);
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(10, i + 4, "" + score.getsScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(10, i + 4, ""));
                            }
                            if (score.getcScore() != null) {
                                ws.addCell(new Label(11, i + 4, "" + score.getcScore(), headerFormat));
                            }
                            if (score.getcScore() == null) {
                                ws.addCell(new Label(11, i + 4, ""));
                            }
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(12, i + 4, "" + score.geteScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(12, i + 4, ""));
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3);
                                ws.addCell(new Label(13, i + 4, dd, headerFormat));
                                tScore3 = (score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3;
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() == null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2);
                                ws.addCell(new Label(13, i + 4, dd, headerFormat));
                                tScore3 = (score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.getcScore()) / 1.7);
                                ws.addCell(new Label(13, i + 4, dd, headerFormat));
                                tScore3 = (score.getsScore() * 0.7 + score.getcScore()) / 1.7;
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.geteScore() * 1.3 + score.getcScore()) / 2.3);
                                ws.addCell(new Label(13, i + 4, dd, headerFormat));
                                tScore3 = (score.geteScore() * 1.3 + score.getcScore()) / 2.3;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() == null) {
                                ws.addCell(new Label(13, i + 4, "" + score.getsScore(), headerFormat));
                                tScore3 = score.getsScore();
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() == null) {
                                ws.addCell(new Label(13, i + 4, "" + score.geteScore(), headerFormat));
                                tScore3 = score.geteScore();
                            }
                            if (score.getsScore() == null && score.geteScore() == null && score.getcScore() != null) {
                                ws.addCell(new Label(13, i + 4, "" + score.getcScore(), headerFormat));
                                tScore3 = score.getcScore();
                            }

                        }
                        if (grade1.getScores().isEmpty()) {
                            ws.addCell(new Label(10, i + 4, ""));
                            ws.addCell(new Label(11, i + 4, ""));
                            ws.addCell(new Label(12, i + 4, ""));
                            ws.addCell(new Label(13, i + 4, ""));
                            tScore3 = 0;
                        }

                    }
                    if (Integer.valueOf(grade1.getGrade()) == g4) {

                        for (int k = 0; k < grade1.getScores().size(); k++) {
                            Score score = grade1.getScores().get(k);
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(14, i + 4, "" + score.getsScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(14, i + 4, ""));
                            }
                            if (score.getcScore() != null) {
                                ws.addCell(new Label(15, i + 4, "" + score.getcScore(), headerFormat));
                            }
                            if (score.getcScore() == null) {
                                ws.addCell(new Label(15, i + 4, ""));
                            }
                            if (score.geteScore() != null) {
                                ws.addCell(new Label(16, i + 4, "" + score.geteScore(), headerFormat));
                            }
                            if (score.geteScore() == null) {
                                ws.addCell(new Label(16, i + 4, ""));
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3);
                                ws.addCell(new Label(17, i + 4, dd, headerFormat));
                                tScore4 = (score.getsScore() * 0.7 + score.geteScore() * 1.3 + score.getcScore()) / 3;
                            }
                            if (score.getsScore() != null && score.geteScore() != null && score.getcScore() == null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2);
                                ws.addCell(new Label(17, i + 4, dd, headerFormat));
                                tScore4 = (score.getsScore() * 0.7 + score.geteScore() * 1.3) / 2;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.getsScore() * 0.7 + score.getcScore()) / 1.7);
                                ws.addCell(new Label(17, i + 4, dd, headerFormat));
                                tScore4 = (score.getsScore() * 0.7 + score.getcScore()) / 1.7;
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() != null) {
                                DecimalFormat fnum = new DecimalFormat("##0.00");
                                String dd = fnum.format((score.geteScore() * 1.3 + score.getcScore()) / 2.3);
                                ws.addCell(new Label(17, i + 4, dd, headerFormat));
                                tScore4 = (score.geteScore() * 1.3 + score.getcScore()) / 2.3;
                            }
                            if (score.getsScore() != null && score.geteScore() == null && score.getcScore() == null) {
                                ws.addCell(new Label(17, i + 4, "" + score.getsScore(), headerFormat));
                                tScore4 = score.getsScore();
                            }
                            if (score.getsScore() == null && score.geteScore() != null && score.getcScore() == null) {
                                ws.addCell(new Label(17, i + 4, "" + score.geteScore(), headerFormat));
                                tScore4 = score.geteScore();
                            }
                            if (score.getsScore() == null && score.geteScore() == null && score.getcScore() != null) {
                                ws.addCell(new Label(17, i + 4, "" + score.getcScore(), headerFormat));
                                tScore4 = score.getcScore();
                            }

                        }
                        if (grade1.getScores().isEmpty()) {
                            ws.addCell(new Label(14, i + 4, ""));
                            ws.addCell(new Label(15, i + 4, ""));
                            ws.addCell(new Label(16, i + 4, ""));
                            ws.addCell(new Label(17, i + 4, ""));
                            tScore4 = 0;
                        }

                    }
                }

                if (tScore1 != 0 && tScore2 != 0 && tScore3 != 0 && tScore4 != 0) {
                    double tScore = (tScore1 + tScore2 + tScore3 + tScore4) / 4;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 != 0 && tScore3 != 0 && tScore4 == 0) {
                    double tScore = (tScore1 + tScore2 + tScore3) / 3;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 != 0 && tScore3 == 0 && tScore4 != 0) {
                    double tScore = (tScore1 + tScore2 + tScore4) / 3;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 == 0 && tScore3 != 0 && tScore4 != 0) {
                    double tScore = (tScore1 + tScore3 + tScore4) / 3;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 != 0 && tScore3 == 0 && tScore4 == 0) {
                    double tScore = (tScore1 + tScore2) / 2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 == 0 && tScore3 != 0 && tScore4 == 0) {
                    double tScore = (tScore1 + tScore3) / 2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 == 0 && tScore3 == 0 && tScore4 != 0) {
                    double tScore = (tScore1 + tScore4) / 2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 != 0 && tScore2 == 0 && tScore3 == 0 && tScore4 == 0) {
                    double tScore = tScore1;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 != 0 && tScore3 != 0 && tScore4 != 0) {
                    double tScore = (tScore2 + tScore3 + tScore4) / 3;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 != 0 && tScore3 != 0 && tScore4 == 0) {
                    double tScore = (tScore2 + tScore3) / 2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 != 0 && tScore3 == 0 && tScore4 != 0) {
                    double tScore = (tScore2 + tScore4) / 2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 != 0 && tScore3 == 0 && tScore4 == 0) {
                    double tScore = tScore2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 == 0 && tScore3 != 0 && tScore4 != 0) {
                    double tScore = (tScore3 + tScore4) / 2;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 == 0 && tScore3 != 0 && tScore4 == 0) {
                    double tScore = tScore3;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (tScore1 == 0 && tScore2 == 0 && tScore3 == 0 && tScore4 != 0) {
                    double tScore = tScore4;
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(tScore);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }

            }

            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return "page/out";

    }

    /*
    *督导评教模板下载
     */
    @RequestMapping(value = "/download", method = RequestMethod.GET)
    public void downloadCouncilor(HttpServletResponse response) throws IOException, WriteException {

        SimpleDateFormat dt = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = dt.format(new Date());//获取当前时间

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流

        response.setHeader("Content-disposition", "attachment; filename="+new String("督导评教模板".getBytes("GB2312"),"8859_1")+".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wwb;

        try {
            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                    12,
                    WritableFont.BOLD,
                    false,
                    UnderlineStyle.NO_UNDERLINE);

            WritableCellFormat formatTitles = new WritableCellFormat(font);
            formatTitles.setWrap(true);

            WritableCellFormat headerFormat = new WritableCellFormat(
                    font);

            //设置单元格文本居中显示

            //水平居中
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);


            wwb = Workbook.createWorkbook(os);
            WritableSheet ws = wwb.createSheet("督导评教模板", 0);

            ws.setColumnView(1,15);
            ws.setColumnView(2,15);
            ws.setColumnView(3,15);
            ws.setColumnView(4,15);
            ws.setColumnView(5,15);
            ws.setColumnView(6,15);
            ws.setColumnView(7,15);
            ws.setColumnView(8,15);
            ws.setColumnView(9,15);
            ws.setColumnView(10,15);
            ws.setColumnView(11,15);
            ws.setColumnView(12,15);
            ws.setColumnView(13,15);

            ws.addCell(new Label(0, 0, "院系",headerFormat));

            ws.addCell(new Label(1, 0, "专业",headerFormat));

            ws.addCell(new Label(2, 0, "年级",headerFormat));

            ws.addCell(new Label(3, 0, "授课教师",headerFormat));

            ws.addCell(new Label(4, 0, "课程",headerFormat));

            ws.addCell(new Label(5, 0, "时间（节数）",headerFormat));

            ws.addCell(new Label(6, 0, "地点",headerFormat));

            ws.addCell(new Label(7, 0, "班级人数",headerFormat));

            ws.addCell(new Label(8, 0, "分数",headerFormat));

            ws.addCell(new Label(9, 0, "优点",headerFormat));

            ws.addCell(new Label(10, 0, "建议",headerFormat));

            ws.addCell(new Label(11, 0, "学生问题",headerFormat));

            ws.addCell(new Label(12, 0, "听课教师",headerFormat));

            ws.addCell(new Label(13, 0, "听课次数",headerFormat));

            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
